package db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

@SuppressWarnings("unused")
public class DBHelper {
	/** Connection variable */
	Connection conn;
	/** Opens a connection to the database and create the 
	 * Prepared Statements needed to access the Movie and Review tables */
	/** Test the database by storing and reading records */
	
	public static void main (String[] args){
		@SuppressWarnings("unused")
		DBHelper helper = new DBHelper();
		
		//ArrayList<Movie> allMovies = helper.getGenresList();
		
		/*for (int i=0; i< allMovies.size();i++){
		//list the movies
			allMovies.get(i);
			}*/
		
	/** Test info to verify if the records are stored and readable */
		//helper.addMovie("Titanic", "James Cameron", "Dunno", 195, "Drama", "1997", "");
		//helper.addReview("A huge, thrilling three-and-a-quarter-hour experience",7.5,5);
				
		}
	/** Admin username
	 * 
	 */	
	public static final String adminUsername = "Admin"; 
	/** Admin password
		 * 
		 */
	public static final String adminPassword = "Admin";
	
	/** Statement to validate user login */
	PreparedStatement validateUserStatement;
	
	/** Statement to add new user */
	PreparedStatement registerUserStatement;
	
	/** Statement to validate user does not already exist */
	PreparedStatement validateUserDoesNotExistStatement;
	
	
	/** validate user login 
	 * @param userName	user name
	 * @param userPassword		user password 
	 
	 */
	
	public int validateUserLogin(String phoneNumber, String userPassword){
		int validUser = 0;
		try{
			validateUserStatement.setString(1, userPassword);
			validateUserStatement.setString(2, phoneNumber);
			
			System.out.println("run validateUserLogin query");
			ResultSet rs = validateUserStatement.executeQuery();
			while (rs.next()){
				validUser = rs.getInt("userLoginValid");
				
			}
			rs.close();
			
			
		}
	
		catch (Exception e) {
			System.out.println("Error in validateUserLogin: "+ e.getClass().getName()+":"+e.getMessage());
			
		}
		return validUser;
	}
	/** Adds a new user 
	 * @param userName	user name
	 * @param userPhoneNumber	user phone number
	 * @param userEmailAddress	user email
	 * @param userPassword		user password 
	 * @param userBirthDay	user birthday 
	 
	 */
	
	public int registerUsers(String userName, String userPhoneNumber, String userEmailAddress,
	   		 String userPassword, String userBirthDay){
		int userExist2 = 0;
		try{
			validateUserDoesNotExistStatement.setString(1, userPhoneNumber);
			
			System.out.println(userName);
			System.out.println(userPhoneNumber);
			System.out.println(userEmailAddress);	
			System.out.println(userPassword);
			System.out.println(userBirthDay);
							
			ResultSet rs = validateUserDoesNotExistStatement.executeQuery();
			while (rs.next()){
				userExist2 = rs.getInt("UserExist");//see is userExist already
				
			}
			System.out.println(userExist2);
			rs.close();
			if(userExist2 == 0)
				{
				registerUserStatement.setString(1, userName);
				registerUserStatement.setString(2, userPhoneNumber);
				registerUserStatement.setString(3, userEmailAddress);
				registerUserStatement.setString(4, userPassword);
				registerUserStatement.setString(5, userBirthDay);
				registerUserStatement.executeUpdate();
				return userExist2;
				}
			else
			{
				return userExist2;
			
			}

		}
	
		catch (Exception e) {
			
			System.out.println("Error in registerUsers: "+ e.getClass().getName()+":"+e.getMessage());
			userExist2 = 2;
		}
		return userExist2;
	}
	
	public DBHelper(){
	
	try{
		String driver ="com.mysql.jdbc.Driver";

		Class.forName(driver);
		//conn = DriverManager.getConnection("jdbc:mysql://localhost/frontera",
			//"root","password");
		//conn = DriverManager.getConnection("jdbc:mysql://23.21.155.37/mist7570",
			//"root","password1");
		conn = DriverManager.getConnection("jdbc:mysql://ebiz.terry.uga.edu:22/frontera",
				"frontera","");
		
		System.out.println("Connected to MySQL");
		validateUserStatement = conn.prepareStatement(
		"SELECT IFNULL(COUNT(*),0) AS userLoginValid  FROM customer " +
		" where password = ? and PhoneNumber = ?");				
//IFNULL(COUNT(*),0)
		validateUserDoesNotExistStatement = conn.prepareStatement(
				"SELECT IFNULL(COUNT(*),0) AS UserExist  FROM customer " +
				" where PhoneNumber = ? ");
		
		registerUserStatement = conn.prepareStatement(
		 "INSERT INTO customer(Name,PhoneNumber,EmailAddress,Password,birthday)" +
		 " VALUES(?,?,?,?,?);");

		/*addMovieStatement = conn.prepareStatement(
				 "insert into movie (movieTitle, movieDirector, movieWriter, movieTime, movieGenre, movieYear, movieImage) values(?, ?, ?, ?, ?, ?, ?)");
		 
		addReviewStatement = conn.prepareStatement(
				 "insert into review (reviewComment, reviewRating, movie_movieID) values(?,?,?)");				 
		deleteReviewStatement = conn.prepareStatement("Delete from review where reviewID = ?");
		
		DeleteMovieStatement = conn.prepareStatement("Delete from movie where movieID = ?");
		
		listMoviesStatement = conn.prepareStatement(
				 "select * from movie");				 
		getMaxMoviesIDStatement = conn.prepareStatement("select max(movieID) as maxID from movie");
		listMovieInfoStatement = conn.prepareStatement(
				"select reviewID,movie_movieID, movieTitle, reviewComment, reviewRating from movie, review where movie.movieID = review.movie_movieID and movieID=?");				
		ArrayList<Review> moviereviewinfo =getMovieInfo(1);  
		 for (int i=0;i<moviereviewinfo.size();i++) {
		     Review reviewList  = moviereviewinfo.get(i);
		     System.out.println(reviewList.getReviewComments());
		     System.out.println(reviewList.getReviewRating());
		 }
		*/
	}
	catch (Exception e){
		System.out.println("Error in constructor: "+e.getClass().getName()+":"+ e.getMessage());
	}		

}
}
